//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2006 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   COMMITMENT1BA
// AUTHOR           :   Alex Cheung 
// DATE CREATE      :   2006/06/28
// DESCRIPTION      :   a COMMON DAO file for the function, COMMITMENT1BA
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2006/06/28   Alex Cheung     1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.catalog.catgroup.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.common.select.dao.SelectDAO;
import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.app.catalog.catgroup.bizLogic.CatalogGroupCommonUtil;
import com.erp.app.catalog.catgroup.criteria.CatalogGroupCriteria;
import com.erp.app.catalog.catgroup.vo.CatalogGroupVO;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

/**
* <p>A <strong>CommonDao</strong> provides database access functions
*  for delegate classes.
*
* @author          Alex Cheung 
* @version 1.0     2006/06/28
* @since           FrameWork 1.1
*
*/
public class CatalogGroupCommonDao {

	private CatalogGroupCommonUtil util = new CatalogGroupCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Alex Cheung 
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		CatalogGroupCriteria criteria = (CatalogGroupCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = " ORDER BY A.DESCRIPTION, A.NODE_ID ";

		sql = "SELECT A.NODE_ID,A.DESCRIPTION, A.CATALOG_LEVEL, "+
           " A.CREATE_USER,A.CREATE_DATE,A.COMPANY_CODE, A.STATUS, '"
			+ criteria.getUserId() + "' USERID, a.BROWSER " + 
           " FROM T_CATALOG A "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		
		Logger.sql(sql);
		
		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Alex Cheung 
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {

		CatalogGroupVO vo = new CatalogGroupVO();

    vo.setL01nodid(rs.getBigDecimal("NODE_ID"));    
    
    vo.setCreateUser(rs.getString("CREATE_USER"));
    vo.setL01crecomcod(rs.getString("COMPANY_CODE"));
    vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
    vo.setL01dsc(rs.getString("DESCRIPTION"));
    
    vo.setL01lvl(rs.getBigDecimal("CATALOG_LEVEL"));
    
    vo.setUserId(rs.getString("USERID"));
    vo.setL01bwr(CommonUtil.bigDecimalToStr(rs.getBigDecimal("BROWSER")));

		return vo;

	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Alex Cheung 
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(CatalogGroupCriteria criteria) {
		StringBuffer whereClause = new StringBuffer();
		whereClause.append("WHERE 1=1");
    	whereClause.append(" AND  A.CATALOG_LEVEL = 1 ");
    	if(!CommonUtil.isEmpty(criteria.getUserId())){
    		whereClause.append(" AND NOT EXISTS (SELECT 1 FROM  T_CATALOG_SECURITY_DTL B WHERE B.USER_CODE = " +
    			"'" + criteria.getUserId()+"' AND  B.NODE_ID = A.NODE_ID ) " 
    			+" AND  a.BROWSER = "  + ProjectConstants.CATALOG_BWR_SHOW  );
    	}
    	return whereClause.toString();
	}

    public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {
        // set the search criteria class
        SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();

        try {
            String query = getHdrSqlQuery(criteria);
            stmt = conn.createStatement();
            stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);
            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    rslt.add(getHdrSearchResultSetValues(rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
}
